// 云函数入口文件
const cloud = require('wx-server-sdk')
const nodeExcel = require('excel-export')
const path = require('path')
const MAX_LIMIT = 100

cloud.init({ env: cloud.DYNAMIC_CURRENT_ENV })

const db = cloud.database()
const bill = db.collection('bill')

// 云函数入口函数
exports.main = async (event, context) => {
  console.log(event)
  
  switch (event.action) {
    case 'getPageBill': {       //流水页账单
      return getPageBill(event)
    }
    case 'getTotal': {          //流水页统计
      return getTotal(event)
    }
    case 'getBillCategory': {   //获取指定范围内账单的所有类型
      return getBillCategory(event)
    }
    case 'updateCategory': {    //修改类型时同步修改账单的类型
      return updateCategory(event)
    }
    case 'getCateData': {       //统计账单类型收支
      return getCateData(event)
    }
    case 'getMonthData': {      //统计月度收支
      return getMonthData(event)
    }
    case 'getExcel': {
      var res = await countExcelData(event)

      if (res.data && res.data.length > 0) {
        return getExcel(event, res.data)
      }
      return {
        errMsg: 'error',
        result: '数据不存在'
      }
    }
  }
}

/**
 * 修改类别名称
 */
async function updateCategory(event) {
  const { OPENID } = cloud.getWXContext()

  return await db.collection('bill').where({
    _openid: OPENID,
    category: event.oldCate
  }).update({
    data: {
      category: event.newCate
    }
  })
}

/**
 * 分页查询账单
 */
async function getPageBill(event) {
  const { OPENID } = cloud.getWXContext()

  const _ = db.command

  var matchInfo = {
    _openid: OPENID,
    date: _.and(_.gte(event.begin), _.lte(event.end))
  }

  if (event.searchKey && event.searchKey.length > 0) {
    matchInfo['remark'] = { $regex: `.*${event.searchKey}.*`, $options: 'i' }
  }

  if (event.filter && event.filter.length > 0) {
    matchInfo['category'] = _.in(event.filter)
  }

  return await bill.where(
      matchInfo
    )
    .orderBy(event.sort, event.direct)
    .orderBy('addtime', 'desc')
    .skip((event.page - 1) * event.size)
    .limit(event.size)
    .field({
      _openid: false
    }).get()
}

/**
 * 按时间段统计收支
 */
async function getTotal(event) {
  const { OPENID } = cloud.getWXContext()

  const $ = db.command.aggregate
  
  var matchInfo = [
    { _openid: OPENID },
    { date: { $gte: event.begin, $lte: event.end } }
  ]

  if (event.searchKey && event.searchKey.length > 0) {
    matchInfo.push({ remark: { $regex: `.*${event.searchKey}.*`, $options: 'i' } })
  }

  // 聚合操作找不到 field in array得操作，这里使用正则代替
  if (event.filter && event.filter.length > 0) {
    var reg = event.filter.join('|')
    matchInfo.push({ category: { $regex: reg, $options: 'i' } })
  }

  return await bill
    .aggregate()
    .match(
      $.and(
        matchInfo
      )
    )
    .group({
      _id: '$type',
      money: $.sum('$money'),
      count: $.sum(1)
    })
    .end()
}

/**
 * 按时间段统计类别
 */
async function getBillCategory(event) {
  const { OPENID } = cloud.getWXContext()

  const $ = db.command.aggregate
  return await bill
    .aggregate()
    .match(
      $.and([
        { _openid: OPENID },
        { date: { $gte: event.begin, $lte: event.end } }
      ])
    )
    .group({
      _id: '$type',
      category: $.addToSet('$category')
    })
    .end()
}

/**
 * 按类型统计
 */
async function getCateData(event) {
  const { OPENID } = cloud.getWXContext()

  const db = cloud.database()
  const $ = db.command.aggregate

  return await db.collection('bill')
    .aggregate()
    .match(
      $.and([
        { _openid: OPENID },
        { date: { $gte: event.begin, $lte: event.end } }
      ])
    )
    .group({
      _id: {
        type: '$type',
        category: '$category'
      },
      money: $.sum('$money')
    })
    .sort({
      'money': -1
    })
    .end()
}

/**
 * 分组查询每月数据
 */
async function getMonthData(event) {
  const { OPENID } = cloud.getWXContext()

  const db = cloud.database()
  const $ = db.command.aggregate

  return await db.collection('bill')
    .aggregate()
    .match(
      $.and([
        { _openid: OPENID },
        { date: { $gte: event.begin, $lte: event.end } }
      ])
    )
    .group({
      _id: {
        type: '$type',
        month: $.substr(['$date', 0, 7])
      },
      money: $.sum('$money')
    })
    .limit(100) // 不写默认20条，一年收支=24条，可能出现数据查不到
    .sort({
      '_id.month': 1
    })
    .end()
}


/**
 * 获取数据
 */
async function countExcelData(event) {
  const { OPENID } = cloud.getWXContext()
  const db = cloud.database()
  const _ = db.command

  // 统计总数
  const countResult = await db.collection('bill')
    .where({
      _openid: OPENID,
      date: _.and([_.gte(event.begin), _.lte(event.end)])
    })
    .count()
  const total = countResult.total
  if (total <= 0) {
    return {
      data: []
    }
  }
  // 计算批次
  const batchTimes = Math.ceil(total / 100)
  // 存放查询的promise数组
  const tasks = []
  for (let i = 0; i < batchTimes; i++) {
    const promise = db.collection('bill').where({
      _openid: OPENID,
      date: _.and([_.gte(event.begin), _.lte(event.end)])
    }).skip(i * MAX_LIMIT).limit(MAX_LIMIT).orderBy(event.sort, event.direct).get()
    tasks.push(promise)
  }
  // 等待所有
  return (await Promise.all(tasks)).reduce((acc, cur) => {
    return {
      data: acc.data.concat(cur.data),
      errMsg: acc.errMsg
    }
  })
}

/**
 * 生成Excel,保存至云存储
 */
async function getExcel(event, jsonData) {
  const { OPENID } = cloud.getWXContext()

  // 转换成excel流数据
  let conf = {
    stylesXmlFile: path.resolve(__dirname, 'styles.xml'),
    name: 'sheet',
    cols: [
      { caption: '日期', type: 'string' },
      { caption: '收支', type: 'string' },
      { caption: '金额', type: 'number' },
      { caption: '类别', type: 'string' },
      { caption: '备注', type: 'string' }
    ],
    rows: jsonToArray(jsonData)
  }
  let result = nodeExcel.execute(conf) // result为excel二进制数据流

  // 上传到云存储
  return await cloud.uploadFile({
    cloudPath: `download/sheet_${OPENID}.xlsx`, // excel文件名称及路径，即云存储中的路径
    fileContent: Buffer.from(result.toString(), 'binary'),
  })
}

/**
 * json对象转换成数组填充
 */
function jsonToArray(arrData) {
  let arr = []
  arrData.forEach(item => {
    arr.push([
      item['date'],
      item['type'] ? '收入' : '花费',
      item['money'],
      item['category'],
      item['remark']
    ])
  })
  return arr
}